﻿/****** Object:  StoredProcedure [dbo].[USP_GXBZ_EMR_XYCF]    Script Date: 2018/12/10 18:44:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_XYCF]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_XYCF]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********
[版本号]1.0.0.0.0
[创建时间]2018-12-10
[作者]Z_DD
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--西药处方GXBZ_EMR_XYCF数据上传
[返回值]
[调用的USP]
[调用实例] EXEC USP_GXBZ_EMR_XYCF
[修改历史]

**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_XYCF]    
AS 
  SET XACT_ABORT ON 
DECLARE
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN                                                                                                  
	
	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_XYCF')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_XYCF','2015-07-01')
	END 
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_XYCF'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM MZYZMXB(NOLOCK)
	
	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN

	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_XYCF(
		YZLSH VARCHAR(64) NULL,
		GRJBXXBSH VARCHAR(64) NULL,
		XGBZ VARCHAR(1) NULL,
		CXJMJKDABH VARCHAR(17) NULL,
		MJZBSH VARCHAR(64) NULL,
		CFBH VARCHAR(64) NULL,
		CFKLKSDM VARCHAR(64) NULL,
		CFKLKSMC VARCHAR(32) NULL,
		YLJGDM VARCHAR(22) NULL,
		YLJGMC VARCHAR(64) NULL,
		KFSJ DATETIME NULL,
		KFSSGH VARCHAR(64) NULL,
		KFYSXM VARCHAR(30) NULL,
		WDGLJGDM VARCHAR(22) NULL,
		WDGLJGMC VARCHAR(70) NULL,
		SHYJSGH VARCHAR(64) NULL,
		SHYJSXM VARCHAR(30) NULL,
		TPYJSGH VARCHAR(64) NULL,
		TPYJSXM VARCHAR(30) NULL,
		HDYJSGH VARCHAR(64) NULL,
		HDYJSXM VARCHAR(30) NULL,
		FYYJSGH VARCHAR(64) NULL,
		FYYJSXM VARCHAR(30) NULL,
		XYZDDM VARCHAR(20) NULL,
		XYZDMC VARCHAR(100) NULL,
		YWSYTJDM VARCHAR(6) NULL,
		YWSYTJMC VARCHAR(100) NULL,
		YWYCSL DECIMAL(18,4) NULL,
		YWSYPL VARCHAR(20) NULL,
		YWJXDM VARCHAR(3) NULL,
		YWJXMC VARCHAR(50) NULL,
		YPDM VARCHAR(20) NULL,
		YPMC VARCHAR(100) NULL,
		YWSYZSL decimal(18,4) NULL,
		YPGG VARCHAR(128) NULL,
		CFTS DECIMAL(3,0) NULL,
		CFYPZH VARCHAR(32) NULL,
		XMJE DECIMAL(10,2) NULL,
		BZ VARCHAR(1024) NULL,
		MJ VARCHAR(16) NULL,
		TBRQ DATETIME NULL,
		SCZT VARCHAR(1) NULL,
		CS_JYBZ INT NULL,
		SCSJ DATETIME NULL
	) ON [PRIMARY]

	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_XYCF (YZLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, MJZBSH, CFBH, CFKLKSDM, CFKLKSMC, 
		YLJGDM, YLJGMC, KFSJ, KFSSGH, KFYSXM, WDGLJGDM, WDGLJGMC,SHYJSGH, SHYJSXM, TPYJSGH, TPYJSXM, 
		HDYJSGH, HDYJSXM, FYYJSGH, FYYJSXM, XYZDDM, XYZDMC,YWSYTJDM, 
		YWSYTJMC, YWYCSL, YWSYPL, YWJXDM, YWJXMC, YPDM, YPMC, YPGG, YWSYZSL, CFTS, CFYPZH, 
		 XMJE, BZ, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ) 
	SELECT
		A.YZLSH YZLSH,    
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS GRJBXXBSH,
		A.XGBZ AS XGBZ,
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS CXJMJKDABH,
		A.JZLSH AS MJZBSH,
		A.CFH AS CFBH,
		A.JZKSDM AS CFKLKSDM,
		A.JZKSMC AS CFKLKSMC,
		isnull((SELECT SPTDM FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGDM,
		C.JGMC AS YLJGMC,
		A.KFSJ AS KFSJ,
		A.KFSSGH AS KFSSGH,
		A.KFYSXM AS KFYSXM,
		'500103000000001' AS WDGLJGDM,
		'渝中区卫计委' AS WDGLJGMC,
		A.SHYJSGH AS SHYJSGH,
		A.SHYJSXM AS SHYJSXM,
		A.TPYJSGH AS TPYJSGH,
		A.TPYJSXM AS TPYJSXM,
		A.HDYJSGH AS HDYJSGH,
		A.HDYJSXM AS HDYJSXM,
		A.FYYJSGH AS FYYJSGH,
		A.FYYJSXM AS FYYJSXM,
		D.XYZDDM AS XYZDDM,
		D.XYZDMC AS XYZDMC,
		A.YWSYTJDM AS YWSYTJDM,
		A.YWSYTJMC AS YWSYTJMC,
		A.YWYCSL AS YWYCSL,
		A.YWSYPLMC AS YWSYPL,
		A.YWJXDM AS YWJXDM,
		A.YWJXMC AS YWJXMC,
		A.YPDMYBBM AS YPDM,
		A.YPMC AS YPMC,
		A.YPGG AS YPGG,
		A.YWSYZSL AS YWSYZSL,		
		A.CFTS AS CFTS,
		A.YZBH AS CFYPZH,
		A.XMJE AS XMJE,
		A.BZ AS BZ,
		A.MJ AS MJ,
		A.TBRQ AS TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ
	FROM MZYZMXB A(NOLOCK) 
	LEFT JOIN SYS_ZCJG_DZ C(NOLOCK) ON A.YLJGDM=C.QPTDM
	LEFT JOIN EMR_MJZBL D(NOLOCK) ON A.YLJGDM=D.YLJGDM AND A.JZLSH=D.JZLSH
	LEFT JOIN ZD_CFFLDM E(NOLOCK) ON A.CFFLDM=E.XMDM 
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01') 
	and 
	A.YPLB ='1'
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) is not NULL
	


	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_XYCF A,#TEMP_GXBZ_EMR_XYCF B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.MJZBSH=B.MJZBSH

	--插入数据至中间库
	INSERT INTO GXBZ_EMR_XYCF (YZLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, MJZBSH, CFBH, JZKSDM, JZKSMC, 
		YLJGDM, YLJGMC, KFSJ, KFSSGH, KFYSXM, WDGLJGDM, WDGLJGMC,SHYJSGH, SHYJSXM, TPYJSGH, TPYJSXM, 
		HDYJSGH, HDYJSXM, FYYJSGH, FYYJSXM, ZDDM, ZDMC,YWSYTJDM, 
		YWSYTJMC, YWYCSL, YWSYPL, YWJXDM, YWJXMC, YPDMYBBM, YPMC, YPGG, YWSYZSL, CFTS, CFYPZH, 
		XMJE, BZ, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ)    
	SELECT YZLSH, GRJBXXBSH, XGBZ, CXJMJKDABH, MJZBSH, CFBH, CFKLKSDM, CFKLKSMC, 
		YLJGDM, YLJGMC, KFSJ, KFSSGH, KFYSXM, WDGLJGDM, WDGLJGMC,SHYJSGH, SHYJSXM, TPYJSGH, TPYJSXM, 
		HDYJSGH, HDYJSXM, FYYJSGH, FYYJSXM, XYZDDM, XYZDMC,  YWSYTJDM, 
		YWSYTJMC, YWYCSL, YWSYPL, YWJXDM, YWJXMC, YPDM, YPMC, YPGG,YWSYZSL,  CFTS, CFYPZH, 
		XMJE, BZ, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ
	FROM #TEMP_GXBZ_EMR_XYCF
	

	COMMIT TRAN

	   SELECT 'T' AS SRESULT, '表：GXBZ_EMR_XYCF数据抽取成功！' AS SHINT
   
	   INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
	   VALUES('表：GXBZ_EMR_XYCF数据抽取成功','GXBZ_EMR_XYCF',GETDATE(),'T','USP_GXBZ_EMR_XYCF')  
   
	
	    --更新上传时间
		UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_XYCF'

	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_XYCF数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN

		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_XYCF数据抽取失败','GXBZ_EMR_XYCF',GETDATE(),'F','USP_GXBZ_EMR_XYCF',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END
